package org.jsea.meta.api.service;

import org.jsea.meta.api.bean.KV;
import org.jsea.meta.api.bean.MetaApiDefinitionBean;
import org.jsea.meta.api.bean.MetaApiException;
import org.jsea.meta.api.bean.MetaApiField;
import org.jsea.meta.api.service.dialect.IMetaApiDialect;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.StringWriter;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.*;

@ConditionalOnBean(value = MetaApiApplicationContext.class)
@Service(value = "MetaApiExecutor")
public class MetaApiExecutor {

    @Resource
    private DataSource source;
    @Resource
    private IMetaApiDialect dialect;

    private static boolean isStringValue(Class<?> inValueType) {
        return CharSequence.class.isAssignableFrom(inValueType) || StringWriter.class.isAssignableFrom(inValueType);
    }

    private static boolean isDateValue(Class<?> inValueType) {
        return Date.class.isAssignableFrom(inValueType) && !java.sql.Date.class.isAssignableFrom(inValueType) && !Time.class.isAssignableFrom(inValueType) && !Timestamp.class.isAssignableFrom(inValueType);
    }

    public static void setValue(PreparedStatement ps, int paramIndex, Object inValue) throws SQLException {
        if (inValue == null) {
            ps.setNull(paramIndex, Types.NULL);
        } else if (isStringValue(inValue.getClass())) {
            ps.setString(paramIndex, inValue.toString());
        } else if (isDateValue(inValue.getClass())) {
            ps.setTimestamp(paramIndex, new Timestamp(((Date) inValue).getTime()));
        } else if (inValue instanceof Calendar cal) {
            ps.setTimestamp(paramIndex, new Timestamp(cal.getTime().getTime()), cal);
        } else if (inValue instanceof BigDecimal) {
            ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
        } else {
            ps.setObject(paramIndex, inValue);
        }
    }

    private static void bindvalue(PreparedStatement statement, Collection<?> values) throws SQLException {
        int index = 1;
        for (Object value : values) {
            setValue(statement, index, value);
            index++;
        }
    }

    public final List<Map<String, Object>> castList(ResultSet rs, MetaApiDefinitionBean bean) throws Exception {
        ResultSetMetaData md = rs.getMetaData();
        dialect.rs(rs);
        List<Map<String, Object>> list = new ArrayList<>(bean.getSize() == null ? 20 : bean.getSize());
        int cc = md.getColumnCount();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<>(cc);
            for (int ind = 1; ind <= cc; ind++) {
                map.put(dialect.formatColumn(md.getColumnLabel(ind)), rs.getObject(ind));
            }
            list.add(map);
        }
        return list;
    }

    public final Map<String, Object> castMap(ResultSet rs) throws Exception {
        ResultSetMetaData md = rs.getMetaData();
        int cc = md.getColumnCount();
        Map<String, Object> map = new HashMap<>(cc);
        if (rs.next()) {
            //ResultSet为空时，直接get会报错
            for (int ind = 1; ind <= cc; ind++) {
                map.put(dialect.formatColumn(md.getColumnLabel(ind)), rs.getObject(ind));
            }
        }
        return map;
    }

    public final Map<String, Object> castInsertMap(ResultSet rs, String[] back) throws Exception {
        if (back.length == 1) {
            if (rs.next()) {
                Map<String, Object> map = new HashMap<>(1);
                map.put(back[0], rs.getObject(1));
                return map;
            }
            return Collections.emptyMap();
        }
        return castMap(rs);
    }

    public List<Map<String, Object>> query(MetaApiQueryHandler handler) {
        handler.setDialect(dialect);
        MetaApiDefinitionBean bean = handler.getBean();
        try (Connection connection = source.getConnection();
             PreparedStatement statement = getPreparedStatement(connection, handler.sql(), handler.getParam());
             ResultSet rs = statement.executeQuery()) {
            return castList(rs, bean);
        } catch (Exception e) {
            throw new MetaApiException(handler.getBean().getFrom() + ' ' + e.getMessage(), handler.sql());
        }
    }

    public Map<String, Object> queryOne(MetaApiQueryHandler handler) {
        handler.setDialect(dialect);
        try (Connection connection = source.getConnection();
             PreparedStatement statement = getPreparedStatement(connection, handler.sql(), handler.getParam());
             ResultSet rs = statement.executeQuery()) {
            return castMap(rs);
        } catch (Exception e) {
            throw new MetaApiException(handler.getBean().getFrom() + ' ' + e.getMessage(), handler.sql());
        }
    }

    public int executeUpdate(String sql, Collection<?> values, String name) {
        try (Connection connection = source.getConnection();
             PreparedStatement statement = getPreparedStatement(connection, sql, values)) {
            return statement.executeUpdate();
        } catch (Exception e) {
            throw new MetaApiException(name + ' ' + e.getMessage(), sql);
        }
    }

    @SuppressWarnings("SqlSourceToSinkFlow")
    public PreparedStatement getPreparedStatement(Connection connection, String sql, Collection<?> values)
            throws Exception {

        PreparedStatement statement = connection.prepareStatement(sql,
                ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY,
                ResultSet.HOLD_CURSORS_OVER_COMMIT);
        bindvalue(statement, values);
        return statement;
    }

    /**
     * . 牺牲插入性能，保证插入顺序和能正确的返回id
     * Oracle在批量插入的情况下，如果设置back，则会产生如下报错
     * java.lang.ArrayIndexOutOfBoundsException: Index 22 out of bounds for length 22
     * java.sql.SQLException: 违反协议: [8, 1]
     * <p>
     * jiang.hy 2024-02-23, 17:43, 周五.
     *
     * @param bean bean
     * @param tds  tds
     * @return back list
     */
    @SuppressWarnings("SqlSourceToSinkFlow")
    public List<Object> doBatchInsert(MetaApiDefinitionBean bean,
                                      List<KV<List<MetaApiField>, Collection<Object>>> tds) {
        List<Object> backs = new ArrayList<>(tds.size());
        MetaApiInsertHandler handler = new MetaApiInsertHandler(bean);
        Map<List<MetaApiField>, PreparedStatement> cache = new HashMap<>();
        try (Connection connection = source.getConnection()) {
            connection.setAutoCommit(false);
            for (KV<List<MetaApiField>, Collection<Object>> td : tds) {
                try {
                    PreparedStatement statement = cache.get(td.key());
                    if (statement == null) {
                        bean.setColumn(td.key());
                        statement = connection.prepareStatement(handler.sql(), bean.getBack());
                        cache.put(td.key(), statement);
                    }
                    bindvalue(statement, td.val());
                    statement.executeUpdate();
                    backs.add(castInsertMap(statement.getGeneratedKeys(), bean.getBack()));
                    connection.commit();
                } finally {
                    for (PreparedStatement statement : cache.values()) {
                        try {
                            if (statement != null) statement.close();
                        } catch (Exception ignored) {
                        }
                    }
                }
            }
        } catch (Exception e) {

            /* [ jiang.hy 2024-02-23, 17:44, 周五 ]
             . 如果发生异常,则会自动回滚
             -                                  */
            throw new MetaApiException(bean.getFrom() + ' ' + e.getMessage(), null);
        }
        return backs;
    }


    /* [ jiang.hy 2024-02-23, 17:44, 周五 ]
     . 方法不能加final,否则source=null
     -                                  */
    public int queryCount(String sql, Collection<?> values, String name) {
        try (Connection connection = source.getConnection();
             PreparedStatement statement = getPreparedStatement(connection, sql, values);
             ResultSet resultSet = statement.executeQuery()) {
            resultSet.next();
            return resultSet.getInt(1);
        } catch (Exception e) {
            throw new MetaApiException(name + ' ' + e.getMessage(), sql);
        }
    }

    @Async
    public void doBatchInsertAsync(MetaApiDefinitionBean bean,
                                   List<KV<List<MetaApiField>, Collection<Object>>> tds) {
        doBatchInsert(bean, tds);
    }
}
